 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ShippingStatusReportSelectCommand]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ShippingStatusReportSelectCommand]
GO

CREATE         PROCEDURE dbo.ShippingStatusReportSelectCommand
	@IsCompleted bit = 0,
	@OfficeID int
AS
	SET NOCOUNT ON;

	SELECT ID, REQ, 
		CASE 
			WHEN OrderDate IS NOT NULL AND OrderDate > '1/1/1900' THEN OrderDate
		END OrderDate,			
		ContainerSize, LP, PONo_Office, PONo_Type, PONo_Supplier, PONo_Number, IsPrimary, 
		CASE 
			WHEN PONo_Consolidation <> 0 THEN PONo_Consolidation
		END PONo_Consolidation,		 
		AccNo, CustomerOrLocation, FinalDestination, [Description], 
		CASE 
			WHEN FactoryETD IS NOT NULL AND FactoryETD > '1/1/1900' THEN FactoryETD
		END FactoryETD,			

		CASE 
			WHEN ShippingDate IS NOT NULL AND ShippingDate > '1/1/1900' THEN ShippingDate
		END ShippingDate,			

		ShippingLine, Vessel, ContainerNo, 

		CASE 
			WHEN ETA IS NOT NULL AND ETA > '1/1/1900' THEN ETA
		END ETA,			

		[Week], ArrivalPort, PI, Payment, DocReceived, 
		CASE 
			WHEN CompletedDate IS NOT NULL AND CompletedDate > '1/1/1900' THEN CompletedDate
		END CompletedDate,
		BulkType, 
		CASE 
			WHEN BulkOrderNo <> 0 THEN BulkOrderNo
		END BulkOrderNo,
		[PayAuth],
		CASE 
			WHEN ContainerDeliveried IS NOT NULL AND ContainerDeliveried > '1/1/1900' THEN ContainerDeliveried
		END ContainerDeliveried,
		ModifiedDate,
		getDate() CurrentDate,
		CASE 
			WHEN POInOpera IS NOT NULL AND POInOpera > '1/1/1900' THEN POInOpera
		END POInOpera,	
		CASE 
			WHEN InvValue <> 0 THEN InvValue
		END InvValue,		
		CASE 
			WHEN PayDate IS NOT NULL AND PayDate > '1/1/1900' THEN PayDate
		END PayDate,		
		Remarks,
		SuppOrderNo,
		CASE 
			WHEN SInvoiced <> 0 THEN SInvoiced
		END SInvoiced,
		OnHold,
		ShippingStatusReport.[OfficeID],
		CustPONo,
		Offices.OfficeCode as [BulkOrderOfficeCode],
		[SInvoicedPd],
		[Catalogue]
	FROM ShippingStatusReport LEFT JOIN Offices ON ShippingStatusReport.[BulkOrderOfficeID] = Offices.OfficeID
	WHERE ShippingStatusReport.OfficeID=@OfficeID AND ((@IsCompleted = 0 AND (CompletedDate IS NULL OR CompletedDate = '1/1/1900'))
		OR (@IsCompleted <> 0 AND CompletedDate IS NOT NULL AND CompletedDate > '1/1/1900'))
		
	ORDER BY REQ, IsPrimary
	
GO
